package com.zss.poi;

import com.zss.pojo.Expenditure;
import com.zss.pojo.vo.ExpenditureStatistics;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.util.ResourceUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.util.*;

public class ExpenditurePOIUtils {

    private static final String UPLOAD_DIR = "/excel/";



    public static void main(String[] args) throws IOException {
        Workbook wb = new XSSFWorkbook(); // 创建新的Excel 工作簿
        Sheet sheet = wb.createSheet("My Sheet"); // 创建新的工作表

        // 创建行和单元格
        Row row = sheet.createRow(10);
        Cell cell = row.createCell(0);

        // 设置单元格合并
        CellRangeAddress region = new CellRangeAddress(10, 10, 1, 2); // 合并第一行第一列到第二列
        sheet.addMergedRegion(region);
        // 设置单元格内容
        cell.setCellValue("Hello, World!");

//        FileOutputStream fos = new FileOutputStream(
//                "C:\\Users\\lizexing\\Desktop\\POI使用FileOutputStream输出流生成的工作簿.xls");
//        wb.write(fos);
//        fos.close();
        File file = ResourceUtils.getFile("classpath:excel/expenditureStatisticsDetail.xlsx");
        System.out.println(file.getPath());


    }

    public static void export(HttpServletResponse response, List<ExpenditureStatistics> list, int sumPrice) {

        try {

            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/expenditureStatisticsExport.xlsx");
            InputStream inputStream = resource.getInputStream();

//            File file = ResourceUtils.getFile("classpath:excel/expenditureStatisticsExport.xlsx");
//            FileInputStream inputStream = new FileInputStream(file);
            Workbook wb = new XSSFWorkbook(inputStream);

            CellStyle style = wb.createCellStyle();
            // 设置边框样式
            style.setBorderBottom((short) 1);
            style.setBorderBottom((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);


            Sheet sheetAt = wb.getSheetAt(0);
            for (int i = 0; i < list.size(); i++) {
                Row row = sheetAt.createRow(i+1);
                ExpenditureStatistics expenditureStatistics = list.get(i);


                CreationHelper createHelper = wb.getCreationHelper();
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(
                        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
                Cell cell = row.createCell(0);
                int year = expenditureStatistics.getCreateTime().getYear()+1900;
                int month = expenditureStatistics.getCreateTime().getMonth()+1;
                System.out.println(expenditureStatistics.getCreateTime().getDate());
                LocalDate today = LocalDate.of(year,month,expenditureStatistics.getCreateTime().getDate());// 获取当前日期
                cell.setCellValue(today.toString());
                cell.setCellStyle(cellStyle);


                row.createCell(1).setCellValue(expenditureStatistics.getAname());
                row.createCell(2).setCellValue(expenditureStatistics.getPrice().intValue());

                // 应用样式到单元格
                row.getCell(0).setCellStyle(style);
                row.getCell(1).setCellStyle(style);
                row.getCell(2).setCellStyle(style);

            }
            CellRangeAddress region = new CellRangeAddress(list.size()+1, list.size()+1, 1, 2); // 合并第一行第一列到第二列
            sheetAt.addMergedRegion(region);
            Row row = sheetAt.getRow(list.size()+1);
            row.getCell(0).setCellValue("金额总计：");
            row.getCell(1).setCellValue(sumPrice);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);

            // 创建一个字体
            Font font = wb.createFont();
            font.setBold(true); // 设置字体加粗

            // 创建一个单元格样式，并将字体应用于它
            short cellIndex = 1;
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    public static void detailExport(HttpServletResponse response, List<Expenditure> list, int sumPrice) {


//        String classpath = System.getProperty("user.dir");
//        String exportFileName = "expenditureStatisticsDetail.xlsx";
//        // 保存文件
//        File file = new File(classpath+UPLOAD_DIR+exportFileName);
//        System.out.println(classpath+UPLOAD_DIR+exportFileName);
        try {
//            File file = ResourceUtils.getFile("classpath:excel/expenditureStatisticsDetail.xlsx");
//            FileInputStream inputStream = new FileInputStream(file);

            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/expenditureStatisticsDetail.xlsx");
            InputStream inputStream = resource.getInputStream();

            Workbook wb = new XSSFWorkbook(inputStream);

            CellStyle style = wb.createCellStyle();
            // 设置边框样式
            style.setBorderBottom((short) 1);
            style.setBorderBottom((short) 1);
            style.setBorderLeft((short) 1);
            style.setBorderRight((short) 1);

            Sheet sheetAt = wb.getSheetAt(0);
            for (int i = 0; i < list.size(); i++) {
                Row row = sheetAt.createRow(i+1);
                Expenditure expenditure = list.get(i);



                CreationHelper createHelper = wb.getCreationHelper();
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(
                        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
                Cell cell = row.createCell(0);
                int year = expenditure.getCreateTime().getYear()+1900;
                int month = expenditure.getCreateTime().getMonth()+1;
                System.out.println(expenditure.getCreateTime().getDate());
                LocalDate today = LocalDate.of(year,month,expenditure.getCreateTime().getDate());// 获取当前日期
                cell.setCellValue(today.toString());
                cell.setCellStyle(cellStyle);


                row.createCell(1).setCellValue(expenditure.getAname());
                row.createCell(2).setCellValue(expenditure.getUname());
                row.createCell(3).setCellValue(expenditure.getProjectName());
                row.createCell(4).setCellValue(expenditure.getPrice().intValue());



                // 应用样式到单元格
                row.getCell(0).setCellStyle(style);
                row.getCell(1).setCellStyle(style);
                row.getCell(2).setCellStyle(style);
                row.getCell(3).setCellStyle(style);
                row.getCell(4).setCellStyle(style);
            }
            CellRangeAddress region = new CellRangeAddress(list.size()+1, list.size()+1, 1, 4); // 合并第一行第一列到第二列
            sheetAt.addMergedRegion(region);
            Row row = sheetAt.createRow(list.size()+1);
            row.createCell(0).setCellValue("金额总计：");
            row.createCell(1).setCellValue(sumPrice);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            // 创建一个字体
            Font font = wb.createFont();
            font.setBold(true); // 设置字体加粗

            // 创建一个单元格样式，并将字体应用于它
            short cellIndex = 1;
            //CellStyle style = wb.getCellStyleAt(cellIndex);
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            row.getCell(0).setCellStyle(style);
            row.getCell(1).setCellStyle(style);
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void expenditureExport(HttpServletResponse response, List<Expenditure> list) {

//        String classpath = System.getProperty("user.dir");
//        String exportFileName = "expenditureStatisticsDetail.xlsx";
//        // 保存文件
//        File file = new File(classpath+UPLOAD_DIR+exportFileName);
//        System.out.println(classpath+UPLOAD_DIR+exportFileName);

        try {
//            File file = ResourceUtils.getFile("classpath:excel/expenditureStatisticsDetail.xlsx");
//            FileInputStream inputStream = new FileInputStream(file);

            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/expenditureStatisticsDetail.xlsx");
            InputStream inputStream = resource.getInputStream();

            Workbook wb = new XSSFWorkbook(inputStream);

            Sheet sheetAt = wb.getSheetAt(0);
            for (int i = 0; i < list.size(); i++) {
                Row row = sheetAt.createRow(i+1);
                Expenditure expenditure = list.get(i);

                CreationHelper createHelper = wb.getCreationHelper();
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setDataFormat(
                        createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
                Cell cell = row.createCell(0);
                int year = expenditure.getCreateTime().getYear()+1900;
                int month = expenditure.getCreateTime().getMonth()+1;
                System.out.println(expenditure.getCreateTime().getDate());
                LocalDate today = LocalDate.of(year,month,expenditure.getCreateTime().getDate());// 获取当前日期
                cell.setCellValue(today.toString());
                cell.setCellStyle(cellStyle);

                row.createCell(1).setCellValue(expenditure.getAname());
                row.createCell(2).setCellValue(expenditure.getUname());
                row.createCell(3).setCellValue(expenditure.getProjectName());
                row.createCell(4).setCellValue(expenditure.getPrice().intValue());

                CellStyle style = wb.createCellStyle();
                // 设置边框样式
                style.setBorderBottom((short) 1);
                style.setBorderBottom((short) 1);
                style.setBorderLeft((short) 1);
                style.setBorderRight((short) 1);
                // 应用样式到单元格
                row.getCell(0).setCellStyle(style);
                row.getCell(1).setCellStyle(style);
                row.getCell(2).setCellStyle(style);
                row.getCell(3).setCellStyle(style);
                row.getCell(4).setCellStyle(style);
            }
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static void downloadTemplate(HttpServletResponse response) {

//        String classpath = System.getProperty("user.dir");
//        String exportFileName = "expenditureStatisticsDetail.xlsx";
//        // 保存文件
//        File file = new File(classpath+UPLOAD_DIR+exportFileName);
//        System.out.println(classpath+UPLOAD_DIR+exportFileName);
        try {

//            File file = ResourceUtils.getFile("classpath:excel/expenditureStatisticsDetail.xlsx");
//            FileInputStream inputStream = new FileInputStream(file);

            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            Resource resource = resolver.getResource("excel/expenditureStatisticsDetail.xlsx");
            InputStream inputStream = resource.getInputStream();

            Workbook wb = new XSSFWorkbook(inputStream);
            String exportName = UUID.randomUUID().toString().replace("-","")+".xlsx";
            response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(exportName,"UTF-8"));
            //	（6）获取OutputStream对象
            ServletOutputStream out = response.getOutputStream();
            //	（7）将FileOutputStream流写入到buffer缓冲区，将OutputStream将缓冲区中的数据输出到客户端
            wb.write(out);
            //（8）关闭流
            inputStream.close();
            out.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

}
